
//init.jss

var DATABASE = 'EX5';

var USER_TABLE = 'User';
var BOARD_TABLE = 'Board';
var USER_JOINED_BOARD_TABLE = 'User_joined_Board';
var MESSAGE_TABLE = 'Message';
var CATEGORY_TABLE = 'Category';

//TODO make sure it is async (maybe add semaphore)



function asem(fireFunc, initLock) {
    if (initLock)
        this.lock = initLock;
    else
        this.lock = 0;
    this.func = fireFunc;
}

asem.prototype.v = function () {
    this.lock++;
  
}

asem.prototype.p = function () {
    this.lock--;
  
    if (this.lock == 0 && this.func) {

        this.func();
    }
}




var mysql = request.require('mysql');
var client = mysql.createClient({
  user: 'root',
  password: 'root',
});

client.on('error', function(error){
throw error}
);
 
//console.log('Connecting to MySQL...');
 
 var lockReads = new asem(initDoneCb);
 
 lockReads.v();
  
	client.query('DROP DATABASE IF EXISTS '+DATABASE, function(err) {
  if (err && err.number != mysql.ERROR_DB_CREATE_EXISTS) {
    throw err;
  }

  lockReads.p();
  
});

lockReads.v();
client.query('CREATE DATABASE IF NOT EXISTS '+DATABASE, function(err) {
  if (err && err.number != mysql.ERROR_DB_CREATE_EXISTS) {
    throw err;
  }
});

    // If no callback is provided, any errors will be emitted as `'error'`
    // events by the client
    client.query('USE '+DATABASE, function(error, results) {
        if(error) {
            console.log('ClientConnectionReady Error: ' + error.message);
            client.end();
            throw error;
        }
        ClientReady(client);
    });


ClientReady = function(client)
{

client.query(
  'CREATE  TABLE IF NOT EXISTS '+USER_TABLE+
  '(username VARCHAR(70)  , '+
  'password VARCHAR(70) NOT NULL , '+
  'name VARCHAR(100) NOT NULL , '+
  'picture TEXT, '+
  'PRIMARY KEY (username))',function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }


	  lockReads.p();
  
	 
  }
);

lockReads.v();


client.query(
  'CREATE  TABLE IF NOT EXISTS '+CATEGORY_TABLE+
  '(id INT  NOT NULL , '+
  'Name VARCHAR(70) NOT NULL , '+
  'PRIMARY KEY (id))',function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	  lockReads.p();
	 
  }
);


lockReads.v();


client.query(
  'CREATE  TABLE IF NOT EXISTS '+BOARD_TABLE+
  '(name VARCHAR(70)  , '+
  'title VARCHAR(70) NOT NULL , '+
  'Category_id INT NOT NULL , '+
  'Owner_username VARCHAR(70) NOT NULL, '+
   'PRIMARY KEY (name),'+
   'FOREIGN KEY (Category_id) REFERENCES Category(Id),'+
   'FOREIGN KEY (Owner_username) REFERENCES User(username))',function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	  lockReads.p();
	 
  }
);


lockReads.v();

client.query(
  'CREATE  TABLE IF NOT EXISTS '+USER_JOINED_BOARD_TABLE+
  '(username VARCHAR(70) NOT NULL , '+
  'board_name VARCHAR(70) NOT NULL , '+
   'FOREIGN KEY (username) REFERENCES User(username),'+
   'FOREIGN KEY (board_name) REFERENCES Board(Name))',function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	  lockReads.p();
	 
  }
);

lockReads.v();

client.query(
  'CREATE  TABLE IF NOT EXISTS '+MESSAGE_TABLE+
  '(id INT AUTO_INCREMENT , '+
  'board_name VARCHAR(70) NOT NULL , '+
  'Username VARCHAR(70) NOT NULL, '+
   'Deletion_date DATE , '+
   'Creation_date DATE NOT NULL , '+
    'Title VARCHAR(70) NOT NULL, '+
    'Text TEXT NOT NULL, '+
    'Color VARCHAR(70) NOT NULL, '+
	'PRIMARY KEY (id),'+
	'FOREIGN KEY (Username) REFERENCES User(username),'+
	'FOREIGN KEY (board_name) REFERENCES Board(Name))',function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }

	  lockReads.p();

	 
  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_TABLE+' '+
  'SET username = ?, password = ?, name = ?,picture= ?',
  ['baruch', 'baruch', 'Baruch Lerner','../images/famImg.jpg'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_TABLE+' '+
  'SET username = ?, password = ?, name = ?,picture= ?',
  ['', '', 'jon doe','../images/famImg.jpg'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);


lockReads.v();

client.query(
  'INSERT INTO '+USER_TABLE+' '+
  'SET username = ?, password = ?, name = ?,picture= ?',
  ['shimi', 'shimi', 'Shimi Bernholtz','../images/famImg.jpg'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_TABLE+' '+
  'SET username = ?, password = ?, name = ?,picture= ?',
  ['admin', 'admin', 'Admin','http://profile.ak.fbcdn.net/hprofile-ak-snc4/49942_4_1525300_n.jpg'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);


	  lockReads.p();

  }
);


lockReads.v();

client.query(
  'INSERT INTO '+CATEGORY_TABLE+' '+
  'SET id=?,name = ?',
  [12,'NAND'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+CATEGORY_TABLE+' '+
  'SET id=?,name = ?',
  [2,'Cars'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+CATEGORY_TABLE+' '+
  'SET id=?,name = ?',
  [3,'Animals'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+BOARD_TABLE+' '+
  'SET name = ?, title = ?, category_id = ?,owner_username= ?',
  ['Horses', 'Horses', 3,'baruch'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);


lockReads.v();

client.query(
  'INSERT INTO '+BOARD_TABLE+' '+
  'SET name = ?, title = ?, category_id = ?,owner_username= ?',
  ['Mazda', 'Mazda', 2,'baruch'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+BOARD_TABLE+' '+
  'SET name = ?, title = ?, category_id = ?,owner_username= ?',
  ['NAND', 'NAND', 12,'baruch'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);
	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_JOINED_BOARD_TABLE+' '+
  'SET username = ?, board_name = ?',
  ['shimi', 'Mazda'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_JOINED_BOARD_TABLE+' '+
  'SET username = ?, board_name = ?',
  ['shimi', 'NAND'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);
	  lockReads.p();

  }
);

lockReads.v();


client.query(
  'INSERT INTO '+USER_JOINED_BOARD_TABLE+' '+
  'SET username = ?, board_name = ?',
  ['baruch', 'NAND'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_JOINED_BOARD_TABLE+' '+
  'SET username = ?, board_name = ?',
  ['shimi', 'Horses'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_JOINED_BOARD_TABLE+' '+
  'SET username = ?, board_name = ?',
  ['baruch', 'Horses'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);


	  lockReads.p();

  }
);

lockReads.v();

client.query(
  'INSERT INTO '+USER_JOINED_BOARD_TABLE+' '+
  'SET username = ?, board_name = ?',
  ['', 'Horses'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);

	  lockReads.p();

  }
);


lockReads.v();

client.query(
  'INSERT INTO '+MESSAGE_TABLE+' '+
  'SET board_name = ?, Username = ?,Creation_date=?,Title=?,Text=?,Color=?',
  ['Horses', 'shimi','2010-01-03','my favorite horses list','shfshfhsf;khfsjfhshfhfhfjhfjhhjghbjhghdfhdfghjkhghgjhgk;hg;khr;ghhghgjhghhhjfhnsdjkhnjhnjjfhdfjhjhhfjsdhbvjbvshdv;kh;vhb','green'],function(error,results){
  
	 if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
         throw error;
      }
	 // console.log('Inserted: ' + results.affectedRows + ' row.');
      //console.log('Id inserted: ' + results.insertId);


	  lockReads.p();

  }
);


//  GetData(client);
};

GetData = function(client)
{
  client.query(
    'SELECT * FROM '+USER_TABLE,
  function selectCb(err, results, fields) {
      if (err) {
          console.log('GetData Error: ' + err.message);
          client.end();
           throw err;
      }
      // Uncomment these if you want lots of feedback
      //console.log('Results:');
      //console.log(results);
      //console.log('Field metadata:');
      //console.log(fields);
      //console.log(sys.inspect(results));
 
      if(results.length > 0)
      {
       // var firstResult = results[0];
       // console.log('First Name: ' + firstResult['firstname']);
       // console.log('Last Name: ' + firstResult['lastname']);
      //  console.log('Message: ' + firstResult['message']);
      }

	//console.log(results);
	//console.log(fields);
	
  });

 // var dbController = request.require('./dbController'); 
 // dbController.initDBClient(client); 
//  var apcontext=request.getApplicationContext();
//  apcontext.client=client;
//  response.end();
};


function initDoneCb(){

  var dbController = request.require('./dbController'); 
  dbController.initDBClient(client); 
  var apcontext=request.getApplicationContext();
  apcontext.client=client;
  response.end();
}

